package com.apes.fn.scm.retailOrder.service.tc.vipSurplus.report;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

/**
 * @Title: VipSurplusDaily
 * @Description: 会员联盟日报表
 * @author: ZhengZeXian
 * @create: 2022/1/14 9:22
 */
public interface VipSurplusDailyRepository extends CustomRepository<VipSurplusDaily,String> {

    /**
     * 根据日期查询会员联盟日报表数据
     * 所有字段对应的情况下 必须给一个随机id 才能转换成实体对象，
     */
    @Query(value = "SELECT sys_guid() id," +
            "           NULL create_date," +
            "           NULL last_updated_stamp," +
            "           NULL update_date," +
            "           'done' state, " +
            "           a.ssgsid as belong_company_id," +
            "           a.gsid as use_company_id," +
            "           a.gsid as company_id," +
            "           '1007' as platform_company_id," +
            "           'K' || a.ssgsid as customer_id," +
            "           'G' || a.gsid as supplier_id," +
            "           'expenses' as type,"+
            "           a.tjrq as collect_date," +
            "           a.bmid as consumer_dept_id ," +
            "           a.ssbmid as belong_dept_id," +
            "           a.sycz as use_savings," +
            "           a.czyj as give_savings," +
            "           a.syfxje as user_cash_back," +
            "           a.fxjeyj as give_cash_back," +
            "           a.syzshb as use_gift_red," +
            "           a.zshbyj as give_gift_red," +
            "           a.sydhq as use_voucher," +
            "           a.dhqyj as give_voucher," +
            "           a.total as give_amount_total," +
            "           a.sybbhb as use_bao_red," +
            "           a.bbhbyj as give_bao_red," +
            "           a.hbyjse as give_red_tax," +
            "           a.bbhbyjse as give_bao_red_tax" +
            "           FROM vip_surplus_rbb a where trunc(a.tjrq) = :date",nativeQuery = true)
    List<VipSurplusDaily> findByDate(@Param("date") Date date);
}
